using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;
using Sinacor.Infra.Service.Data;
using Sinacor.Infra.Service.Security.Authorization;
using Sinacor.Infra.Service.Common;

namespace Sinacor.Servico.Bovespa.Corretagem.ResourceAccess
{

    /// <summary>
    /// Business Logic TabelaPadrao
    /// </summary>
    public class TabelaPadrao : BaseResourceAccess
    {
        #region Construtor

        /// <summary>
        /// Construtor padrão
        /// </summary>
        public TabelaPadrao()
            : base()
        {
        }

        #endregion


        #region ConsultarProximoNumeroTabelaPadrao
        /// <summary>
        /// Método ConsultarProximoNumeroTabelaPadrao
        /// </summary>
        public System.Int32? ConsultarProximoNumeroTabelaPadrao()
        {
            System.Int32? response = null;
            StringBuilder sql = new StringBuilder();

            sql.Append(@"select max(cd_tabela)+1 AS MAX_CD_TABELA from TBOCORR_TAB");

            using (DbCommand command = this.DataBase.GetSqlStringCommand(sql.ToString()))
            {

                response = this.FindOne<System.Int32?>(command, new ConstructEntity<System.Int32?>(ConstrutorConsultarProximoNumeroTabelaPadrao));
            }

            return response;
        }

        #endregion

        #region ConstrutorConsultarProximoNumeroTabelaPadrao
        protected System.Int32? ConstrutorConsultarProximoNumeroTabelaPadrao(IDataReader reader)
        {
            return reader.GetValue<System.Int32?>("MAX_CD_TABELA");
        }
        #endregion

        #region ConsultarTabelaPadrao
        /// <summary>
        /// Método ConsultarTabelaPadrao
        /// </summary>
        public List<BusinessEntity.TabelaPadrao> ConsultarTabelaPadrao(BusinessEntity.Parameter.ConsultarTabelaPadrao consultartabelapadrao)
        {
            List<BusinessEntity.TabelaPadrao> responses = null;
            StringBuilder sql = new StringBuilder();

            sql.Append(@"SELECT distinct
							TBOCORR_POLI.CD_POLITICA, 
							tbocorr_tab.CD_TABELA, 
							tbocorr_tab.DS_TABELA ,
							tbocorr_tab.IN_MODO_APLC_TAB, 
							tbocorr_tab.VL_MINIMO, 
							tbocorr_tab.VL_MAXIMO , 
							tbocorr_tab.IN_UTIL_DESC_DIF 
							from tbocorr_tab,TBOCORR_POLI
							where tbocorr_tab.CD_TABELA = TBOCORR_POLI.cd_tabela
							and tbocorr_tab.cd_tabela = :CD_TABELA");
 
            if (consultartabelapadrao.Codigo != null) {
                sql.Append(@" and (
							TBOCORR_POLI.CD_POLITICA = :CD_POLITICA
							  and TBOCORR_POLI.dt_inicial = :DT_INICIAL 
							)
							or 
							(
							TBOCORR_POLI.cd_tabela in (select cd_tabela from TBOCORR_VERI where cd_politica = TBOCORR_POLI.cd_politica and TBOCORR_POLI.DT_INICIAL = TBOCORR_VERI.dt_Inicial)
							)
							");
            }

            using (DbCommand command = this.DataBase.GetSqlStringCommand(sql.ToString()))
            {
                this.DataBase.AddInParameter(command, "CD_TABELA", DbType.Int32, consultartabelapadrao.CodigoTabelaPadrao);
                if (consultartabelapadrao.Codigo != null)
                {
                    this.DataBase.AddInParameter(command, "CD_POLITICA", DbType.Int32, consultartabelapadrao.Codigo);
                    this.DataBase.AddInParameter(command, "DT_INICIAL", DbType.DateTime, consultartabelapadrao.DataInicial);
                }
                responses = this.FindAll<BusinessEntity.TabelaPadrao>(command, new ConstructEntity<BusinessEntity.TabelaPadrao>(ConstrutorConsultarTabelaPadrao));
            }

            return responses;
        }

        #endregion

        #region ConstrutorConsultarTabelaPadrao
        protected BusinessEntity.TabelaPadrao ConstrutorConsultarTabelaPadrao(IDataReader reader)
        {
            BusinessEntity.TabelaPadrao entity = new BusinessEntity.TabelaPadrao();

            entity.CodigoPoliticaCorretagem = reader.GetValue<System.Int32>("CD_POLITICA");
            entity.Codigo = reader.GetValue<System.Int32>("CD_TABELA");
            entity.Descricao = reader.GetValue<System.String>("DS_TABELA");
            entity.ModoAplicacao = EnumConverter.Parse<Sinacor.Servico.Bovespa.Corretagem.BusinessEntity.Enum.ModoAplicacao>(reader.GetValue<string>("IN_MODO_APLC_TAB"));
            entity.IndicadorDescontoDiferenciado = (reader.GetValue<System.String>("IN_UTIL_DESC_DIF").ToUpper().Trim() == "S") ? true: false;
            entity.ValorMaximo = reader.GetValue<System.Decimal>("VL_MAXIMO");
            entity.ValorMinimo = reader.GetValue<System.Decimal>("VL_MINIMO");

            return entity;
        }
        #endregion

        #region ExcluirTabelaPadrao
        /// <summary>
        /// Método ExcluirTabelaPadrao
        /// </summary>
        public void ExcluirTabelaPadrao(BusinessEntity.TabelaPadrao tabelapadrao)
        {
            StringBuilder sql = new StringBuilder();

            sql.Append(@"delete from tbocorr_tab
							where cd_tabela = :CD_TABELA");

            using (DbCommand command = this.DataBase.GetSqlStringCommand(sql.ToString()))
            {
                this.DataBase.AddInParameter(command, "CD_TABELA", DbType.Int32, tabelapadrao.Codigo);

                this.DataBase.ExecuteNonQuery(command);

            }


        }

        #endregion


        #region SalvarTabelaPadrao
        /// <summary>
        /// Método SalvarTabelaPadrao
        /// </summary>
        public void SalvarTabelaPadrao(Sinacor.Servico.Bovespa.Corretagem.BusinessEntity.TabelaPadrao tabelapadrao)
        {
            StringBuilder sql = new StringBuilder();

            sql.Append(@"insert into TBOCORR_TAB
							(
							CD_TABELA,
							DS_TABELA,
							IN_MODO_APLC_TAB,
							VL_MINIMO,
							IN_UTIL_DESC_DIF,
							VL_MAXIMO
							)
							values
							(
							:CD_TABELA,
							:DS_TABELA,
							:IN_MODO_APLC_TAB,
							:VL_MINIMO,
							:IN_UTIL_DESC_DIF,
							:VL_MAXIMO
							)");

            using (DbCommand command = this.DataBase.GetSqlStringCommand(sql.ToString()))
            {
                this.DataBase.AddInParameter(command, "CD_TABELA", DbType.Int32, tabelapadrao.Codigo);
                this.DataBase.AddInParameter(command, "DS_TABELA", DbType.String, tabelapadrao.Descricao);
                string enumModoAplicacao = EnumConverter.ToString<Sinacor.Servico.Bovespa.Corretagem.BusinessEntity.Enum.ModoAplicacao>(tabelapadrao.ModoAplicacao);
                this.DataBase.AddInParameter(command, "IN_MODO_APLC_TAB", DbType.String, enumModoAplicacao);
                this.DataBase.AddInParameter(command, "VL_MINIMO", DbType.Decimal, tabelapadrao.ValorMinimo);
                this.DataBase.AddInParameter(command, "VL_MAXIMO", DbType.Decimal, tabelapadrao.ValorMaximo);
                this.DataBase.AddInParameter(command, "IN_UTIL_DESC_DIF", DbType.String, ((tabelapadrao.IndicadorDescontoDiferenciado) ? "S" : "N"));

                this.DataBase.ExecuteNonQuery(command);

            }


        }

        #endregion


    }
}



